﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data.SqlClient;
using Models;

namespace DAL
{
    public class LableService
    {
   
        public bool SearchTable(string UserName)
        {
            SqlConnection conn = ConnPoll.GetConn();
            bool tempbool = false;
            string sql = "select * from Lable"+ UserName;
            ConnPoll.Open();
            SqlCommand com = new SqlCommand(sql, conn);
            SqlDataReader dr = com.ExecuteReader();
            while (dr.Read())
            {
                tempbool = true;
                break;
            }
            dr.Close();
            com = null;
            ConnPoll.Close();
            return tempbool;
        }

        #region 创建标签表
        /// <summary>
        /// 创建标签表
        /// </summary>
        /// <param name="UserName"></param>
        public void CreateLable(string UserName)
        {
            SqlConnection conn = ConnPoll.GetConn();
            try
            {
                ConnPoll.Open();
                string tbna = "Lable" + UserName;
                string sql = " use bds249611391_db"
                    + " create table " + tbna
                    + " ("
                    + " LableId int not null identity(1,1),"//主键Id
                    + " LableName nvarchar(200) not null"//婚姻状况表关系列
                    + " )"
                    + " use bds249611391_db"
                    + " alter table " + tbna
                    + " add constraint PK_LableId primary key (LableId)";
                SqlCommand com = new SqlCommand(sql, conn);
                com.ExecuteNonQuery();
                com = null;
                ConnPoll.Close();
            }
            catch (Exception)
            {

            }
        }
        #endregion

        #region 新增信息
        /// <summary>
        /// 新增信息
        /// </summary>
        /// <param name="l"></param>
        /// <param name="UserName"></param>
        /// <returns></returns>
        public int Insert(Lable l,string UserName)
        {
            SqlConnection conn = ConnPoll.GetConn();
            string sql = "insert into Lable" + UserName + " (LableName)" + " values" + " (@LableName)";
            return SQLHelper.ExecuteNonQuery(conn,System.Data.CommandType.Text,sql, new SqlParameter("@LableName", l.LableName));
        }
        #endregion

        #region 删除信息
        /// <summary>
        /// 删除信息
        /// </summary>
        /// <param name="l"></param>
        /// <param name="UserName"></param>
        /// <returns></returns>
        public int Delete( string UserName)
        {
            SqlConnection conn = ConnPoll.GetConn();
            string sql = "TRUNCATE  table Lable" + UserName;
            return SQLHelper.ExecuteNonQuery(conn,System.Data.CommandType.Text,sql);
        }
        #endregion

        #region 修改信息
        public int Set(Lable l,string setStr, string UserName)
        {
            SqlConnection conn = ConnPoll.GetConn();
            string sql = "update Lable"+ UserName 
                + " set LableName=@setStr"
                + " where LableName=@LableName";
            SqlParameter[] pars = new SqlParameter[]
            {
                new SqlParameter("@LableId",setStr),
                new SqlParameter("@LableName",l.LableName)
            };
            return SQLHelper.ExecuteNonQuery(conn,System.Data.CommandType.Text,sql,pars);
        }
        #endregion

        #region 查新信息
        public List<Lable> SearchAll( string UserName)
        {
            SqlConnection conn = ConnPoll.GetConn();
            List<Lable> ll = new List<Lable>();
            string sql = "select * from Lable" + UserName;
            SqlDataReader dr = SQLHelper.ExecuteReader(conn,System.Data.CommandType.Text,sql);
            while (dr.Read())
            {
                Lable L = new Lable();
                L.LableId = Convert.ToInt32(dr["LableId"]);
                L.LableName = dr["LableName"].ToString();
                ll.Add(L);
            }
            return ll;
        }
        #endregion

        #region 查找库中是否已有此内容
        /// <summary>
        /// 查找库中是否已有此内容
        /// </summary>
        /// <param name="l"></param>
        /// <param name="UserName"></param>
        /// <returns></returns>
        public int Search(Lable l,string UserName)
        {
            SqlConnection conn = ConnPoll.GetConn();
            string sql = "select count(1) from Lable"+ UserName + " where LableName=@LableName";
            return Convert.ToInt32(SQLHelper.ExecuteScalar(conn, System.Data.CommandType.Text, sql, new SqlParameter("@LableName", l.LableName)));
        }
        #endregion
    }
}
